package com.serviceprovider.business.module.sp.transaction.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.user.dao.impl.UserMapper;
import com.serviceprovider.business.module.admin.user.dao.interfaces.IUserDao;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;
import com.serviceprovider.business.module.sp.transaction.model.Batch;
import com.serviceprovider.business.module.sp.transaction.model.TransactionDetail;
import com.serviceprovider.business.module.sp.transaction.model.TransactionUserAmount;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransactionUserAmountDao;
import com.serviceprovider.business.module.user.model.impl.User;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

//~--- JDK imports ------------------------------------------------------------

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Repository("transactionUserAmountDao")
public class TransactionUserAmountDaoImpl extends SimpleJdbcDaoSupport implements ITransactionUserAmountDao {
    protected final Log      logger = LogFactory.getLog(getClass());
    @Qualifier("transUserAmountJdbcInsert")
    @Autowired
    private SimpleJdbcInsert jdbcInsert;
    @Autowired
    private IUserDao         userDao;

    @Autowired
    public TransactionUserAmountDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    @Override
    public TransactionUserAmount insert(TransactionUserAmount transUserAmount) {
        Map<String, Object> para = new HashMap<String, Object>();

        para.put("batch_id", transUserAmount.getBatchId());
        para.put("transaction_id", transUserAmount.getTransactionId());
        para.put("user_id", transUserAmount.getUserId());
        para.put("amount", transUserAmount.getAmount());

        Number id = jdbcInsert.executeAndReturnKey(para);

        transUserAmount.setId(id.intValue());

        return transUserAmount;
    }

    @Override
    public void update(TransactionUserAmount transactionUserAmount) {
        String sql = "Update transaction_user_amount set batch_id=?, transaction_id=?, user_id=?, amount=? where id=?";

        getJdbcTemplate().update(sql, new Object[] { transactionUserAmount.getBatchId(),
                transactionUserAmount.getTransactionId(), transactionUserAmount.getUserId(),
                transactionUserAmount.getAmount(), transactionUserAmount.getId() });
    }

    @Override
    public TransactionUserAmount getById(int id) {
        try {
            String sql = "select * from transaction_user_amount where id=" + id;

            return (TransactionUserAmount) getJdbcTemplate().queryForObject(sql, new TransUserAmountMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void delete(int id) {
        String sql = "delete from transaction_user_amount where id=" + id;

        getJdbcTemplate().execute(sql);
    }

    @Override
    public Page<TransactionUserAmount> find(int pageNumber, int pageSize, String sortColumn, String sortOrder) {
        PaginationHelper<TransactionUserAmount> ph       = new PaginationHelper<TransactionUserAmount>();
        String                                  sqlCount = "SELECT count(*) FROM transaction_user_amount";
        String                                  sqlQuery = "SELECT * FROM transaction_user_amount order by " + sortColumn + " " + sortOrder;
        Object[]                                args     = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args, pageNumber, pageSize, sortColumn, sortOrder,
                            new TransUserAmountMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> findPayeesOfProvider(Integer providerId) {
        String sql = "SELECT distinct(u.id),u.* " + "FROM transaction_details td,transaction_user_amount tx,user u "
                     + "WHERE (td.transaction_id=tx.transaction_id) and (tx.user_id=u.id) " + ((providerId != null)
                ? "and td.user_id=" + providerId
                : "");

        return getJdbcTemplate().query(sql, new UserMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<Integer, Integer> calculateTotalAmount(List<Integer> transactionIds) {
        Map<Integer, Integer> result = new HashMap<Integer, Integer>();

        if ((transactionIds == null) || (transactionIds.size() == 0)) {
            return result;
        }

        StringBuffer sql =
            new StringBuffer(
                "SELECT transaction_id, sum(amount) as total FROM transaction_user_amount where transaction_id in ");
        String concat = "(";

        for (Integer transactionId : transactionIds) {
            sql.append(concat).append("?");
            concat = ",";
        }

        sql.append(")").append(" group by transaction_id");

        List<TransactionTotal> totals = getJdbcTemplate().query(sql.toString(), transactionIds.toArray(),
                                            new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new TransactionTotal(rs.getInt("transaction_id"), rs.getInt("total"));
            }
        });

        for (TransactionTotal total : totals) {
            result.put(total.getTransactionId(), total.getTotal());
        }

        return result;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionUserAmount> findByTransactionDetail(Batch batch, TransactionDetail trans) {
        String sql = "SELECT * FROM transaction_user_amount tu WHERE tu.transaction_id=" + trans.getId()
                     + (((batch != null) && (batch.getId() != null))
                        ? " and tu.batch_id=" + batch.getId()
                        : "");

        if (Logger.getLogger(getClass()).isDebugEnabled()) {
            Logger.getLogger(getClass()).debug(sql);
        }

        List<TransactionUserAmount> transUserAmounts = getJdbcTemplate().query(sql, new TransUserAmountMapper());
        Set<Integer>                userIds          = new HashSet<Integer>();

        for (TransactionUserAmount transUserAmount : transUserAmounts) {
            userIds.add(transUserAmount.getUserId());
        }

        List<User>         users   = userDao.findUsersById(userIds);
        Map<Integer, User> userMap = new HashMap<Integer, User>();

        for (User user : users) {
            userMap.put(user.getId(), user);
        }

        for (TransactionUserAmount transUserAmount : transUserAmounts) {
            transUserAmount.setUser(userMap.get(transUserAmount.getUserId()));
        }

        return transUserAmounts;
    }

    @Override
    public void deleteByBatchId(List<Integer> deletedBatchId) {
        if (deletedBatchId.isEmpty()) {
            return;
        }

        StringBuffer buf    = new StringBuffer("delete from transaction_user_amount where batch_id in (");
        String       concat = "";

        for (Integer id : deletedBatchId) {
            buf.append(concat).append(id);
            concat = ",";
        }

        buf.append(")");
        getJdbcTemplate().execute(buf.toString());
    }

    @Override
    public void deleteByBatchId(Integer batchId) {
        String sql = "delete from transaction_user_amount where batch_id=" + batchId;

        getJdbcTemplate().execute(sql);
    }

    @Override
    public List<TransactionUserAmount> findTransactionUserAmountByTransactionId(int transactionId) {
        String sql = "SELECT * from transaction_user_amount where transaction_id=" + transactionId;

        return getSimpleJdbcTemplate().query(sql, new TransUserAmountMapper());
    }

    @Override
    public TransactionUserAmount findPayeeByIds(int transactionId, int payeeUserId) {
        String sql = "SELECT * from transaction_user_amount where transaction_id=" + transactionId + " and user_id="
                     + payeeUserId;

        try {
            return (TransactionUserAmount) getJdbcTemplate().queryForObject(sql, new TransUserAmountMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void updateAll(TransactionDetail transactionDetail) {
        if (transactionDetail == null) {
            return;
        }

        if (transactionDetail.getId() == null) {
            return;
        }

        if (transactionDetail.getBatch() == null) {
            return;
        }

        String sql = "Update transaction_user_amount set batch_id=? where transaction_id=?";

        getJdbcTemplate().update(sql, new Object[] { transactionDetail.getBatch().getId(), transactionDetail.getId() });
    }

    @Override
    public void deleteAllByTransactionId(Integer transactionId) {
        if (transactionId == null) {
            return;
        }

        String sql = "delete from transaction_user_amount where transaction_id=" + transactionId;

        getJdbcTemplate().execute(sql);
    }

    private class TransUserAmountMapper implements ParameterizedRowMapper<TransactionUserAmount> {
        @Override
        public TransactionUserAmount mapRow(ResultSet rs, int i) throws SQLException {
            TransactionUserAmount transUserAmount = new TransactionUserAmount();

            transUserAmount.setAmount(rs.getInt("amount"));
            transUserAmount.setBatchId(rs.getInt("batch_id"));
            transUserAmount.setId(rs.getInt("id"));
            transUserAmount.setTransactionId(rs.getInt("transaction_id"));
            transUserAmount.setUserId(rs.getInt("user_id"));

            return transUserAmount;
        }
    }


    private class TransactionTotal {
        private int total;
        private int transactionId;

        public TransactionTotal(int transactionId, int total) {
            this.transactionId = transactionId;
            this.total         = total;
        }

        public int getTransactionId() {
            return transactionId;
        }

        public int getTotal() {
            return total;
        }
    }
}



